﻿using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web;
using Novacode;
using System.ComponentModel;

namespace KPIS.GERP.Models.Class
{
    public class MailMerge
    {
        //public bool MergeTemplate(string strTemplateFilePath, string strSaveAsFilePath
        //    , Dictionary<string, string> fields
        //    , Dictionary<string, System.Data.DataTable> dtbs = null
        //    , List<Dictionary<string, string>> Dics = null
        //    , Dictionary<string, bool> areas = null)
        //{
        //    bool bOK = false;

        //    using (DocX document = DocX.Load(strTemplateFilePath))
        //    {
        //        Object oMissing = System.Reflection.Missing.Value;
        //        Object oTemplatePath = strTemplateFilePath;
        //        Application application = new Application();
        //        Microsoft.Office.Interop.Word._Document wordDoc = new Document();

        //        try
        //        {
        //            wordDoc = application.Documents.Add(ref oTemplatePath, ref oMissing, ref oMissing, ref oMissing);

        //            if (fields != null)
        //            {
        //                //foreach (var field in fields)
        //                //{
        //                //    document.ReplaceText("«" + field.Key + "»", field.Value);
        //                //}


        //                foreach (Field myMergeField in wordDoc.Fields)
        //                {
        //                    Range rngFieldCode = myMergeField.Code;
        //                    String fieldText = rngFieldCode.Text;

        //                    if (fieldText.StartsWith(" MERGEFIELD"))
        //                    {
        //                        Int32 endMerge = fieldText.IndexOf("\\");
        //                        Int32 fieldNameLength = fieldText.Length - endMerge;
        //                        String fieldName = fieldText.Substring(11, endMerge - 11);

        //                        fieldName = fieldName.Trim();

        //                        if (fields.ContainsKey(fieldName))
        //                        {
        //                            string strValue = fields[fieldName];
        //                            myMergeField.Select();
        //                            application.Selection.TypeText(strValue);
        //                        }
        //                    }
        //                }
        //            }

        //            if (dtbs != null)
        //            {
        //                foreach (var _dtb in dtbs)
        //                {
        //                    System.Data.DataTable dtb = _dtb.Value;
        //                    for (int i = 0; i < dtb.Rows.Count; i++)
        //                    {
        //                        System.Data.DataRow dr = dtb.Rows[i];

        //                        foreach (System.Data.DataColumn col in dtb.Columns)
        //                        {
        //                            var ColumnName = col.ColumnName;// + (i + 1).ToString();
        //                            string strValue = dr[ColumnName].ToString();

        //                            //document.ReplaceText("«" + ColumnName + "»", strValue);

        //                            foreach (Field myMergeField in wordDoc.Fields)
        //                            {
        //                                Range rngFieldCode = myMergeField.Code;
        //                                String fieldText = rngFieldCode.Text;

        //                                if (fieldText.StartsWith(" MERGEFIELD"))
        //                                {
        //                                    Int32 endMerge = fieldText.IndexOf("\\");
        //                                    Int32 fieldNameLength = fieldText.Length - endMerge;
        //                                    String fieldName = fieldText.Substring(11, endMerge - 11);
        //                                    fieldName = fieldName.Trim();

        //                                    if (ColumnName + (i + 1).ToString() == fieldName)
        //                                    {
        //                                        myMergeField.Select();
        //                                        application.Selection.TypeText(strValue);
        //                                    }
        //                                }
        //                            }
        //                        }
        //                    }

        //                    foreach (System.Data.DataColumn col in dtb.Columns)
        //                    {
        //                        var ColumnName = col.ColumnName;

        //                        //document.ReplaceText("«" + ColumnName + "»", "");

        //                        foreach (Field myMergeField in wordDoc.Fields)
        //                        {
        //                            Range rngFieldCode = myMergeField.Code;
        //                            String fieldText = rngFieldCode.Text;

        //                            if (fieldText.StartsWith(" MERGEFIELD"))
        //                            {
        //                                Int32 endMerge = fieldText.IndexOf("\\");
        //                                Int32 fieldNameLength = fieldText.Length - endMerge;
        //                                String fieldName = fieldText.Substring(11, endMerge - 11);
        //                                fieldName = fieldName.Trim();

        //                                if (fieldName.Contains(ColumnName))
        //                                {
        //                                    myMergeField.Select();

        //                                    if (application.Selection.Text == "«" + fieldName + "»")
        //                                        application.Selection.TypeText(" ");
        //                                }
        //                            }
        //                        }
        //                    }

        //                    //object oEndOfDoc = _dtb.Key;
        //                    //System.Data.DataTable dtb = _dtb.Value;
        //                    //Range wrdRng = wordDoc.Bookmarks[ref oEndOfDoc].Range;//.get_Item(ref oEndOfDoc).Range;
        //                    //var oTable = wordDoc.Tables.Add(wrdRng, dtb.Rows.Count, dtb.Columns.Count, ref oMissing, ref oMissing);
        //                    //oTable.Range.ParagraphFormat.SpaceAfter = 6;
        //                    ////int r, c;
        //                    //string strText;
        //                    //for (int r = 0; r < dtb.Rows.Count; r++)
        //                    //{
        //                    //    for (int c = 0; c < dtb.Columns.Count; c++)
        //                    //    {
        //                    //        strText = dtb.Rows[r][c].ToString();
        //                    //        oTable.Cell(r + 1, c + 1).Range.Text = strText;
        //                    //    }
        //                    //}
        //                }
        //            }

        //            if (Dics != null && Dics.Count > 0)
        //            {
        //                for (int i = 0; i < Dics.Count; i++)
        //                {
        //                    var Dic = Dics[i];

        //                    foreach (var DicItem in Dic)
        //                    {
        //                        string ColumnName = DicItem.Key;// + (i + 1).ToString();
        //                        string strValue = DicItem.Value;

        //                        //document.ReplaceText("«" + ColumnName + "»", strValue);
        //                        foreach (Field myMergeField in wordDoc.Fields)
        //                        {
        //                            Range rngFieldCode = myMergeField.Code;
        //                            String fieldText = rngFieldCode.Text;

        //                            if (fieldText.StartsWith(" MERGEFIELD"))
        //                            {
        //                                Int32 endMerge = fieldText.IndexOf("\\");
        //                                Int32 fieldNameLength = fieldText.Length - endMerge;
        //                                String fieldName = fieldText.Substring(11, endMerge - 11);
        //                                fieldName = fieldName.Trim();

        //                                if (ColumnName + (i + 1).ToString() == fieldName)
        //                                {
        //                                    myMergeField.Select();
        //                                    application.Selection.TypeText(strValue);
        //                                }
        //                            }
        //                        }
        //                    }

        //                    var Dic0 = Dics[0];

        //                    foreach (var DicItem in Dic)
        //                    {
        //                        var ColumnName = DicItem.Key;
        //                        //document.ReplaceText("«" + ColumnName + "»", "");
        //                        foreach (Field myMergeField in wordDoc.Fields)
        //                        {
        //                            Range rngFieldCode = myMergeField.Code;
        //                            String fieldText = rngFieldCode.Text;

        //                            if (fieldText.StartsWith(" MERGEFIELD"))
        //                            {
        //                                Int32 endMerge = fieldText.IndexOf("\\");
        //                                Int32 fieldNameLength = fieldText.Length - endMerge;
        //                                String fieldName = fieldText.Substring(11, endMerge - 11);
        //                                fieldName = fieldName.Trim();

        //                                if (fieldName.Contains(ColumnName))
        //                                {
        //                                    myMergeField.Select();

        //                                    if (application.Selection.Text == "«" + fieldName + "»")
        //                                        application.Selection.TypeText(" ");
        //                                }
        //                            }
        //                        }
        //                    }
        //                }
        //            }

        //            if (areas != null)
        //            {
        //                foreach (var area in areas)
        //                {
        //                    object oEndOfDoc = area.Key;
        //                    bool bShow = area.Value;
        //                    Range wrdRng = wordDoc.Bookmarks[ref oEndOfDoc].Range;//.get_Item(ref oEndOfDoc).Range;
        //                    if (!bShow)
        //                    {
        //                        wrdRng.Delete();
        //                    }
        //                }
        //            }

        //            foreach (Field myMergeField in wordDoc.Fields)
        //            {
        //                Range rngFieldCode = myMergeField.Code;
        //                String fieldText = rngFieldCode.Text;

        //                if (fieldText.StartsWith(" MERGEFIELD"))
        //                {
        //                    Int32 endMerge = fieldText.IndexOf("\\");
        //                    Int32 fieldNameLength = fieldText.Length - endMerge;
        //                    String fieldName = fieldText.Substring(11, endMerge - 11);
        //                    fieldName = fieldName.Trim();
        //                    myMergeField.Select();

        //                    if (application.Selection.Text == "«" + fieldName + "»")
        //                        application.Selection.TypeText(".................");
        //                }
        //            }

        //            //document.SaveAs(strSaveAsFilePath);
        //            wordDoc.SaveAs(strSaveAsFilePath);
        //            application.Application.Quit();
        //            bOK = true;
        //        }
        //        catch (Exception ex)
        //        {
        //            throw ex;
        //        }
        //        finally
        //        {
        //            //wordDoc.Close();
        //            application.Application.Quit();
        //        }
        //    }

        //    return bOK;
        //}

        public bool MergeTemplate(string strTemplateFilePath, string strSaveAsFilePath
            , Dictionary<string, string> fields
            , Dictionary<string, System.Data.DataTable> dtbs = null
            , List<Dictionary<string, string>> Dics = null
            , Dictionary<string, bool> areas = null)
        {
            bool bOK = false;

            using (DocX document = DocX.Load(strTemplateFilePath))
            {
                try
                {
                    if (fields != null)
                    {
                        foreach (var field in fields)
                        {
                            document.ReplaceText("«" + field.Key + "»", field.Value);
                        }
                    }

                    if (dtbs != null)
                    {
                        foreach (var _dtb in dtbs)
                        {
                            System.Data.DataTable dtb = _dtb.Value;
                            for (int i = 0; i < dtb.Rows.Count; i++)
                            {
                                System.Data.DataRow dr = dtb.Rows[i];

                                foreach (System.Data.DataColumn col in dtb.Columns)
                                {
                                    var ColumnName = col.ColumnName + (i + 1).ToString();
                                    string strValue = dr[col.ColumnName].ToString();

                                    document.ReplaceText("«" + ColumnName + "»", strValue);
                                }
                            }
                        }
                    }

                    if (Dics != null && Dics.Count > 0)
                    {
                        for (int i = 0; i < Dics.Count; i++)
                        {
                            var Dic = Dics[i];

                            foreach (var DicItem in Dic)
                            {
                                string ColumnName = DicItem.Key + (i + 1).ToString();
                                string strValue = DicItem.Value;

                                document.ReplaceText("«" + ColumnName + "»", strValue);
                            }
                        }
                    }

                    if (areas != null)
                    {
                    }

                    while (document.Text.IndexOf("«") >= 0)
                    {
                        int Left = document.Text.IndexOf("«");
                        int Right = document.Text.IndexOf("»");

                        string strText = document.Text.Substring(Left, Right - Left + 1);
                        document.ReplaceText(strText, "");
                    }

                    document.SaveAs(strSaveAsFilePath);
                    bOK = true;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                }
            }

            return bOK;
        }

        public void SaveFileToClient(HttpResponse response, string strFilePath, string strFileName, bool bDeleteFile)
        {
            response.Clear();
            response.AddHeader("Content-Disposition", "attachment; filename=" + strFileName);
            response.WriteFile(strFilePath + strFileName);
            response.Flush();
            if (bDeleteFile)
            {
                System.IO.File.Delete(strFilePath + strFileName);
            }
            response.End();
        }

        public string MergeString(string strValue, string strIfEmpty = "...................")
        {
            return string.IsNullOrWhiteSpace(strValue) ? strIfEmpty : strValue;
        }

        public string GetExcelColumnName(int columnNumber)
        {
            int dividend = columnNumber;
            string columnName = String.Empty;
            int modulo;

            while (dividend > 0)
            {
                modulo = (dividend - 1) % 26;
                columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
                dividend = (int)((dividend - modulo) / 26);
            }

            return columnName;
        }

        public bool MergeExcel(HttpResponseBase response, string strTemplateFilePath, string strFileName
            , Dictionary<string, System.Data.DataTable> dtbs = null
            , Dictionary<string, string> Dics = null
            , OfficeOpenXml.Table.TableStyles tableStyle = OfficeOpenXml.Table.TableStyles.None
            , int? addMoreRowCopyFromRow = null
            , int addMoreRow = 0)
        {
            bool bOK = false;

            FileInfo file = new FileInfo(strTemplateFilePath);

            using (ExcelPackage pck = new ExcelPackage(file, true))
            {
                if (pck.Workbook.Worksheets.Count > 0)
                {
                    ExcelWorksheet ws = pck.Workbook.Worksheets[1];

                    if(addMoreRow > 0 && addMoreRowCopyFromRow != null)
                    {
                        ws.InsertRow(addMoreRowCopyFromRow.Value + 1, addMoreRow, addMoreRowCopyFromRow.Value);
                    }

                    if (dtbs != null)
                    {
                        foreach (var dtb in dtbs)
                        {
                            if (dtb.Value.Rows.Count > 0)
                            {
                                ws.Cells[dtb.Key].LoadFromDataTable(dtb.Value, false);
                            }
                        }
                    }

                    if (Dics != null)
                    {
                        foreach (var Dic in Dics)
                        {
                            ws.Cells[Dic.Key].Value = Dic.Value;
                        }
                    }

                    response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    response.AddHeader("content-disposition", string.Format("attachment;  filename={0}.xlsx", HttpUtility.UrlDecode(strFileName, Encoding.UTF8)));
                    response.HeaderEncoding = System.Text.Encoding.UTF8;
                    response.BinaryWrite(pck.GetAsByteArray());
                    pck.Dispose();
                }
            }
            return bOK;
        }

        //public System.Data.DataTable ToDataTable<T>(this IList<T> data)
        //{
        //    PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(T));
        //    System.Data.DataTable table = new System.Data.DataTable();
        //    for (int i = 0; i < props.Count; i++)
        //    {
        //        PropertyDescriptor prop = props[i];
        //        table.Columns.Add(prop.Name, prop.PropertyType);
        //    }
        //    object[] values = new object[props.Count];
        //    foreach (T item in data)
        //    {
        //        for (int i = 0; i < values.Length; i++)
        //        {
        //            values[i] = props[i].GetValue(item);
        //        }
        //        table.Rows.Add(values);
        //    }
        //    return table;
        //}
    }
}
